package org.vnetcon.blobdb.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import org.vnetcon.blobdb.client.BlobDBUIService;

import com.google.appengine.api.users.User;
import com.google.appengine.api.users.UserService;
import com.google.appengine.api.users.UserServiceFactory;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class BlobDBUIServiceImpl extends RemoteServiceServlet implements
		BlobDBUIService {

	static boolean driverRegistered = false;

	private boolean allowCall(){
		boolean bRet = false;
		try{
		    UserService userService = UserServiceFactory.getUserService();

			if (userService.isUserAdmin()) {
				bRet = true;
			}
			
		}catch(Exception ex){
			bRet = false;
		}
		return bRet;
	}

	public String isAdmin(String something) {
		if(!this.allowCall()){
			return "no";
		}else{
			return "yes";
		}
	}
	
	public String getLoginUrl(String bSQL) {
	    UserService userService = UserServiceFactory.getUserService();
//		String ret = userService.createLoginURL(this.getThreadLocalRequest().getRequestURI());
		String ret = userService.createLoginURL("/");
		return ret;
	}

	public String getLogoutUrl(String bSQL) {
	    UserService userService = UserServiceFactory.getUserService();
//		String ret = userService.createLogoutURL(this.getThreadLocalRequest().getRequestURI());
		String ret = userService.createLogoutURL("/");
		return ret;
	}

	public String addUser(String username, String password) {
		String strRet = "OK";
		String bSQLCreate = "CREATE TABLE SYSTEM.USER (150, 4000) ( USERNAME CHAR(50), PASSWORD CHAR(50) );";
		String bSQLInsert = "INSERT INTO SYSTEM.USER (USERNAME, PASSWORD) VALUES ( '" + username + "', '" + password + "' ) ;";
		
		if(!this.allowCall()){
			return "Authentication failed: Only administrator can access this service. Please login as administrator using google account.";
		}
		
		
		try{
			
			if(!driverRegistered){
				Class.forName("org.vnetcon.blobdb.driver.jdbc.BlobDBDriver").newInstance();
				driverRegistered = true;
			}
			

			Connection con = DriverManager.getConnection("blobdb");
			Statement stmt = con.createStatement();
			stmt.executeUpdate(bSQLInsert);
			stmt.close();
		}catch(Exception ex){
			try{
				Connection con = DriverManager.getConnection("blobdb");
				Statement stmt = con.createStatement();
				stmt.executeUpdate(bSQLCreate);
				stmt.executeUpdate(bSQLInsert);
				stmt.close();
			}catch(Exception exx){
				strRet = exx.getMessage();
			}
		}
		
		
		return strRet;
	}	
	
	public String executeQuery(String bSQL) {
		String strRet = "OK";
		StringBuilder sb = new StringBuilder();
		
		if(!this.allowCall()){
			return "Authentication failed: Only administrator can access this service. Please login as administrator using google account.";
		}

		try{
			
			if(!driverRegistered){
				Class.forName("org.vnetcon.blobdb.driver.jdbc.BlobDBDriver").newInstance();
				driverRegistered = true;
			}

			Connection con = DriverManager.getConnection("blobdb");
			Statement stmt = con.createStatement();
			if(bSQL.trim().toUpperCase().startsWith("SELECT") || bSQL.trim().toUpperCase().startsWith("DESC")){
				ResultSet rs = stmt.executeQuery(bSQL);
				ResultSetMetaData rsMeta = rs.getMetaData();
				int iColCount = rsMeta.getColumnCount();
				int i = 0;
				
				sb.append("<table border=\"1\">");
				sb.append("<tr>");
				sb.append("<td><b>");
				sb.append("ROWID");
				sb.append("</b></td>");
				while(i < iColCount){
					sb.append("<td><b>");
					sb.append(rsMeta.getColumnName(i));
					sb.append("</b></td>");
					i++;
				}
				sb.append("</tr>");
				
				while(rs.next()){
					i = 0;
					sb.append("<tr>");
					sb.append("<td>");
					sb.append(rs.getRowId(-1).toString());
					sb.append("</td>");
					while(i < iColCount){
						sb.append("<td>");
						sb.append(rs.getString(i + 1));
						sb.append("</td>");
						i++;
					}
					sb.append("</tr>");
				}
				
				
				sb.append("</table>");
				strRet = sb.toString();
			}else{
				stmt.executeUpdate(bSQL);
				stmt.close();
			}
//			con.close();
			
		}catch(Exception ex){
			strRet = "<b>" + ex.getMessage() + "</b>";
		}
		
		return strRet;
	}
	
	public String greetServer(String input) throws IllegalArgumentException {
		System.out.println("Test started....");
		try{
			String sql = "";
			Class.forName("org.vnetcon.blobdb.driver.jdbc.BlobDBDriver").newInstance();
			
		}catch(Exception ex){
			System.out.println("ERROR IN GREETSERVER: " + ex);
			ex.printStackTrace();
		}
		System.out.println("Test done.");
		return "ok";
	}
	

}
